package connectivity;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Category;
import model.Klant;
import model.Product;
import model.User;
import model.Report;

public class QueryManager {

    private final Dbmanager dbmanager;

    public QueryManager(Dbmanager dbmanager) {
        this.dbmanager = dbmanager;
    }

    public String getCategoryName(int categoryId) {
        String categoryName = "";
        try {
            String sql = "SELECT naam FROM categorie WHERE categorie_id='" + categoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                categoryName = result.getString("naam");
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categoryName;
    }

    public List<Category> getCategories() {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM categorie ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Category category = new Category(result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"));
                categories.add(category);
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return categories;
    }
    
    public Product getProduct(int productId) {
        Product product = new Product();
        try {
            String sql = "SELECT * FROM product WHERE product_id='" + productId + "'";

            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                product = new Product(result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"),
                        result.getInt("voorraad"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return product;
    }

    public List<Product> getProducts(int categoryId) {
        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT * FROM product WHERE categorie_id='" + categoryId + "'";
            //"SELECT * FROM klant ORDER BY " + sortBy + " " + order
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(
                        result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"),
                        result.getInt("voorraad")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
        public void addProduct(Product product) {
        int maxID = 0;
      
        try {
            String SQL_productID = "SELECT max(product_id) as product_id FROM product";
            ResultSet result1 = dbmanager.doQuery(SQL_productID);
            if (result1.next()) {
                maxID = result1.getInt("product_id");
            }
            maxID++;            
            
            //String SQL_product = "INSERT INTO 'product' SET product_id='" + maxID + "', categorie_id='" + product.getCategorieId() + "', naam='" + product.getName() + "', prijs='" + product.getPrice() + "', omschrijving='" + product.getDescription() + "', voorraad='" + product.getStock() + "'";                       
            
            String SQL_product ="INSERT INTO product(product_id, categorie_id, naam, prijs, omschrijving, voorraad)" 
            + " VALUES('"+maxID+"','"+product.getCategorieId()+"','"+product.getName()+"','"+product.getPrice()+"','"+product.getDescription()+"','"+product.getStock()+"')";
            
            dbmanager.updateQuery(SQL_product); 
            System.out.println("New product added: "+product.getName());
            
        } catch (SQLException e) {
            System.out.println("connectivity.QueryManager.setOrder() Exception:" + e.getMessage());
        }
    }
        
        
        public void updateProduct(Product product) {

                     
            String SQL_product = "UPDATE `product` SET categorie_id= '" + product.getCategorieId() + "', naam = '" + product.getName() + "', prijs = '" + product.getPrice() + "', omschrijving = '" + product.getDescription() + "', voorraad = '" + product.getStock() + "' WHERE product_id = '" + product.getProductId() + "'";
                                     
            //="UPDATE INTO product(product_id, categorie_id, naam, prijs, omschrijving, voorraad)" 
            //+ " VALUES('"+product.getProductId()+"','"+product.getCategorieId()+"','"+product.getName()+"','"+product.getPrice()+"','"+product.getDescription()+"','"+product.getStock()+"')";
            
            //System.out.println(SQL_product);
            dbmanager.updateQuery(SQL_product); 
            System.out.println("Product editted: "+product.getName());
            
        
    }


    
         
    /**
     * This function firstly builds a sql query with the use of the category and productName parameters.
     * This parameters are from the filter functions.
     * After that it has executed the query it will loop through the results it got back and
     * fill the products list with a product name and its stock.
     * And when all that is done it will return the list.
     * @param categorie
     *  A string with the category the query needs to filter on.
     * @param productName
     *  A string with the product name the query needs to filter on.
     * @return 
     *  A list with results from the query.
     */
        
    public List<Product> getAllProductsStock(String category, String productName) {

        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT * FROM product ORDER BY naam ASC";
            if (category != null && !category.equals("Alles") && productName == null) {
                sql = "SELECT * FROM product p LEFT JOIN categorie c ON p.categorie_id = c.categorie_id WHERE c.naam = '"+category+"' ORDER BY c.naam ASC";    
            }
            else if("Alles".equals(category) && productName != null) {
                sql = "SELECT * FROM product WHERE naam LIKE '%"+productName+"%' ORDER BY naam ASC";
                
            }
            else if(category != null && !category.equals("Alles") && productName != null) {
                sql = "SELECT * FROM product p LEFT JOIN categorie c ON p.categorie_id = c.categorie_id WHERE c.naam = '"+category+"' AND p.naam LIKE '%"+productName+"%' ORDER BY c.naam ASC";                
            }
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("voorraad"),
                        result.getDouble("prijs"),
                        result.getInt("voorraad")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }
    
    /**
     * This function checks if the product wich is given through the paramters has the same
     * stock as in the database.
     * @param productName
     *  The product name of the current product.
     * @param newProductStock
     *  The product stock of the current product.
     * @return 
     *  If the stock is the same we dont need to update it so than it wil return true.
     *  When the product stock in the database is different from the one given through 
     *  the parameters we return false.
     */
    public boolean CompareProductStock(String productName, int newProductStock) {
        int productStock;
        boolean returnValue = false;
        try {
            String sql = "SELECT voorraad FROM product WHERE voorraad='" + productName + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                productStock = result.getInt("voorraad");
                if (productStock == newProductStock) {
                    returnValue = true;
                }
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return returnValue;
    }
    
    /**
     * Here we update the values of the stock in the database.
     * First we check them if the values are the same.
     * Because than we dont have to update them.
     * If they aren't the same we update the stock of the product.
     * @param productName
     *  The name of the current product.
     * @param productInventory
     *  The stock of the current product.
     * @return 
     *  true if the stock has been updates.
     *  false if this was not necessary.
     */
    public boolean updateProductStock(String productName, int productInventory) {
        boolean skip = CompareProductStock(productName, productInventory);
        if (skip == false) {
            String SQL_order = "UPDATE `product` SET voorraad = '" + productInventory + "' WHERE naam = '" + productName + "'";
            int order_id = 0;
            System.out.println(SQL_order);
            try {
                dbmanager.st.executeUpdate(SQL_order);
            } catch (SQLException e) {
                System.out.println("Exception:" + e.getMessage());
            }
            return true;
        }
        return false;
    }

    public List<Product> getProductList() {
        List<Product> products = new ArrayList<Product>();
        try {


            String sql = "SELECT * FROM product";
            //"SELECT * FROM klant ORDER BY " + sortBy + " " + order
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(
                        result.getInt("product_id"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getDouble("prijs"),
                        result.getInt("voorraad")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return products;
    }

    public void setOrder(model.Basket basket, String naam, String adres,
            String postcode, String woonplaats, String email, String opmerking, String betaalmethode) {
        String SQL_order = "INSERT INTO `order` (naam, adres, postcode, woonplaats, email, notes, betaalmethode, datum, gebruiker_id, order_status_id)"
                + " VALUES('" + naam + "', '" + adres + "', '" + postcode + "', '"
                + woonplaats + "', '" + email + "', '" + opmerking + "', '" + betaalmethode + "', CURDATE(),'1','1')";
        int order_id = 0;
        try {
            ResultSet result = dbmanager.insertQuery(SQL_order);
            result.next();
            order_id = result.getInt(1);
        } catch (SQLException e) {
            System.out.println("connectivity.QueryManager.setOrder() Exception:" + e.getMessage());
        }
        List<Product> products = basket.getProducts();
        for (Product product : products) {
            int product_id = product.getProductId();
            int aantal = basket.getProductAmount(product);
            String SQL_orderProduct = "INSERT INTO orderregel (product_id,order_id,aantal) VALUES (" + product_id + "," + order_id + "," + aantal + ")";
            dbmanager.insertQuery(SQL_orderProduct);
            //Decrease the stock
            Product current_product = this.getProduct(product_id);
            int current_stock = current_product.getStock();
            int new_stock = current_stock - aantal;
            this.updateProductStock(current_product.getName(), new_stock);
        }
    }

    /*
     * Get Klant
     * Created JJ
     */
    public Klant getKlant(int gebruiker_id) {
        Klant klant = new Klant();
        try {
            String sql = "SELECT * FROM klant WHERE gebruiker_id='" + gebruiker_id + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                klant = new Klant(
                        result.getInt("gebruiker_id"),
                        result.getString("naam"),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return klant;
    }
    

    
    /*
     * Delete Application User
     * Created JJ
     */
    public void delUser(int gebruiker_id) {
        String sql = "DELETE FROM gebruikers WHERE gebruiker_id='" + gebruiker_id + "'";
        try {
            dbmanager.st.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
     * Get Application User
     * Created JJ
     * Get user information by username and pwd to login
     * return value`s if input matches
     */
    public User getUser(String user_name, String user_pwd) {
        User user = new User();
        try {
            String sql = "SELECT * FROM gebruikers WHERE gebruikersnaam='" + user_name + "' AND wachtwoord='" + user_pwd + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                user = new User(result.getInt("gebruiker_id"),
                        result.getString("gebruikersnaam"),
                        result.getString("wachtwoord"),
                        result.getInt("rol_id"));
                
                
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return user;
    }
    /*
     * Created JJ
     * get user database information by klant_id
     */
    public User getUser(int gebruiker_id) {
        User user = new User();
        try {
            String sql = "SELECT * FROM gebruikers WHERE gebruiker_id='" + gebruiker_id + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                user = new User(result.getInt("gebruiker_id"),
                        result.getString("gebruikersnaam"),
                        result.getString("wachtwoord"),
                        result.getString("email"),
                        result.getString("aangemaakt_op"),
                        result.getString("laatst_ingelogd"),
                        result.getInt("rol_id"));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return user;
    }

    /*
     * Add User
     * Created Vince Vriend
     */
    public void addUser(User user) {
        int maxID = 0;

        try {
            String SQL_gebruikerID = "SELECT max(gebruiker_id) as gebruiker_id FROM gebruikers";
            ResultSet result1 = dbmanager.doQuery(SQL_gebruikerID);
            if (result1.next()) {
                maxID = result1.getInt("gebruiker_id");
            }
            maxID++;

            String SQL_user = "INSERT INTO `gebruikers` SET gebruiker_id='" + maxID + "', gebruikersnaam='" + user.getGebruikersnaam() + "', wachtwoord='" + user.getWachtwoord() + "', email='" + user.getEmail() + "', aangemaakt_op='" + user.getAangemaakt_op() + "', laatst_ingelogd='" + user.getLaatst_ingelogd() + "', rol_id='" + user.getRol_id() + "'";
            ResultSet result = dbmanager.insertQuery(SQL_user);
            result.next();
            System.out.println("New user added: " + user.getGebruikersnaam());
        } catch (SQLException e) {
            System.out.println("connectivity.QueryManager.setOrder() Exception:" + e.getMessage());
        }
    }

    public void updateUser(User updatedUser, boolean changePassword) {
        String SQL_update;
        if (changePassword == false) {
            SQL_update = "UPDATE `gebruikers` SET gebruikersnaam = '" + updatedUser.getGebruikersnaam() + "', email='" + updatedUser.getEmail() + "', aangemaakt_op='" + updatedUser.getAangemaakt_op() + "', laatst_ingelogd='" + updatedUser.getLaatst_ingelogd() + "', rol_id='" + updatedUser.getRol_id()
                    + "' WHERE gebruiker_id = '" + updatedUser.getGebruiker_id() + "'";
        } else {
            SQL_update = "UPDATE `gebruikers` SET gebruikersnaam = '" + updatedUser.getGebruikersnaam() + "', wachtwoord='" + updatedUser.getWachtwoord() + "', email='" + updatedUser.getEmail() + "', aangemaakt_op='" + updatedUser.getAangemaakt_op() + "', laatst_ingelogd='" + updatedUser.getLaatst_ingelogd() + "', rol_id='" + updatedUser.getRol_id()
                    + "' WHERE gebruiker_id = '" + updatedUser.getGebruiker_id() + "'";
        }
        try {
            dbmanager.st.executeUpdate(SQL_update);
        } catch (SQLException e) {
            System.out.println("Exception:" + e.getMessage());
        }
    }

    public List<User> getUserList() {
        List<User> gebruikers = new ArrayList<User>();
        try {
            String sql = "SELECT * FROM gebruikers";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                gebruikers.add(new User(
                        result.getInt("gebruiker_id"),
                        result.getString("gebruikersnaam"),
                        result.getString("wachtwoord"),
                        result.getString("email"),
                        result.getString("aangemaakt_op"),
                        result.getString("laatst_ingelogd"),
                        result.getInt("rol_id")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return gebruikers;
    }

        
    public void delKlant(List<String> row) {
        String delQuery = "DELETE FROM klant\nWHERE";
        int length = row.size();
        for(int x = 0; x < length - 1; x++) {
            delQuery += " gebruiker_id='"+row.get(x) +"' OR";
        }
        delQuery += " gebruiker_id='"+row.get(length - 1) +"';\n";
        
        dbmanager.insertQuery(delQuery);
        
        delQuery = "DELETE FROM gebruikers\nWHERE";
        for(int x = 0; x < length - 1; x++) {
            delQuery += " gebruiker_id='"+row.get(x) +"' OR";
        }
        delQuery += " gebruiker_id='"+row.get(length - 1) +"';\n";
        dbmanager.insertQuery(delQuery);
    }
    /*
     * Add Klant User
     * Created JJ
     */
    public void addKlant(Klant klant) {
        int maxID = 0;

        try {
            String SQL_klantID = "SELECT max(gebruiker_id) as gebruiker_id FROM gebruikers";
            ResultSet result1 = dbmanager.doQuery(SQL_klantID);
            if (result1.next()) {
                maxID = result1.getInt("gebruiker_id");
            }
            maxID++;

            //Add customer personal information.
            String SQL_klant = "INSERT INTO `klant` SET gebruiker_id='" + maxID + "', naam='" + klant.getNaam() + "', adres='" + klant.getAdres() + "', postcode='" + klant.getPostcode() + "', woonplaats='" + klant.getWoonplaats() + "'";//, email='"+klant.getEmail()+"'";                        
            //Add User information.
            String SQL_user = "INSERT INTO `gebruikers` SET gebruiker_id='" + maxID + "', gebruikersnaam='"
                    + klant.getGebruikersnaam() + "', wachtwoord='" + klant.getWachtwoord() + "', email='"
                    + klant.getEmail() + "', aangemaakt_op='" + klant.getAangemaakt_op() + "', laatst_ingelogd='"
                    + klant.getLaatst_ingelogd() + "', rol_id='" + klant.getRol_id() + "'";

            ResultSet result = dbmanager.insertQuery(SQL_klant);
            result.next();
            result = dbmanager.insertQuery(SQL_user);
            result.next();
            System.out.println("New klant added: " + klant.getNaam());
        } catch (SQLException e) {
            System.out.println("connectivity.QueryManager.setOrder() Exception:" + e.getMessage());
        }
    }
    
    public void updateKlant(int gebruiker_id, String name, String adres, String postalCode, String home) {
            String updateQuery = "UPDATE `klant` SET naam = '" + name + "', "
                    + "adres = '" + adres + "', "
                    + "postcode = '" + postalCode + "', "
                    + "woonplaats = '" + home + "'" 
                    + " WHERE gebruiker_id = '" + gebruiker_id + "'";
            this.dbmanager.insertQuery(updateQuery);
    }
    /*
     * Add Klant User
     * Creater Marco.
     * Search for 'searchFor' through the columns listed in 'searchBy'
     */
    public List<Klant> searchKlantList(List<String> searchBy, String searchFor) {
        List<Klant> klanten = new ArrayList<Klant>();
        try {
            int length = searchBy.size();
            String searchQuery = "";
            for (int x = 0; x < length - 1; x++) {
                searchQuery += searchBy.get(x) + " LIKE '%" + searchFor + "%' OR ";
            }
            searchQuery += searchBy.get(length - 1) + " LIKE '%" + searchFor + "%'";

            String sql = "SELECT * FROM klant \n"
                    + "WHERE " + searchQuery;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                klanten.add(new Klant(
                        result.getInt("gebruiker_id"),
                        result.getString("naam"),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return klanten;
    }
    /*
     * Created JJ
     * 
     */
    public List<Klant> getKlantList(String sortBy, String order) {
        List<Klant> klanten = new ArrayList<Klant>();
        try {
            String sql = "SELECT * FROM klant ORDER BY " + sortBy + " " + order;
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                klanten.add(new Klant(
                        result.getInt("gebruiker_id"),
                        result.getString("naam"),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return klanten;
    }

    /*
     * Get Report
     * Created JJ
     * Make report table select from orderregel left join next tables product, categorie, order
     * Sum aantal and sum total product 
     * aantal * total product sold
     * use filter input`s for WHERE selection datum and categorie
     */
    public List<Report> getOrderList(String sqlEx) {
        List<Report> order = new ArrayList<Report>();
        try {
            String sql = "SELECT `order`.*, product.naam as product_name, product.*,SUM(orderregel.aantal) as aantal, orderregel.*, (SUM(product.prijs)*aantal) as Tprijs,"
                    +"categorie.naam as categorie_name, categorie.* "                
                    +"FROM orderregel "
                    +"LEFT JOIN product ON orderregel.product_id = product.product_id " 
                    +"LEFT JOIN categorie ON product.categorie_id = categorie.categorie_id "
                    +"LEFT JOIN `order` ON orderregel.order_id = order.order_id "
                    + sqlEx +" "              
                    + "GROUP BY product.product_id ";
            //System.out.println(sql);

            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                order.add(new Report(
                        result.getInt("product_id"),
                        result.getString("product_name"),
                        result.getString("categorie_name"),
                        result.getInt("order_id"),
                        result.getInt("aantal"),
                        result.getInt("Tprijs")));
            }
        } catch (SQLException e) {
            System.out.println(Dbmanager.SQL_EXCEPTION + e.getMessage());
        }
        return order;
    }
}
